package com.web.data.dao;
import com.web.data.bean.taxi;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface taxiMysql {
    /**
     * 32个分析
     */
    //1.乘客数量统计
    @Select("select passengercount,count(*)cnt from taxi_data group by passengercount order by cnt desc limit 20")
    List<taxi> passengercount();
    //2.小费金额统计
//    @Select("select tipamount,count(*)cnt from taxi_data group by passengercount order by cnt desc limit 20")
    @Select("select tipamount,count(*)cnt from taxi_data group by tipamount order by cnt desc limit 20")
    List<taxi> tipamount();
    //3.总金额统计
    @Select("select totalamount,count(*)cnt from taxi_data group by totalamount order by cnt desc limit 10")
    List<taxi> totalamount();
    //4.通行费总额统计
    @Select("select tollsamount,count(*)cnt from taxi_data group by tollsamount order by cnt desc limit 10")
    List<taxi> tollsamount();
    //5.其他杂费和附加费统计
    @Select("select extra,count(*)cnt from taxi_data group by extra order by cnt desc limit 10")
    List<taxi> extra();
    //6.行程距离统计
    @Select("select tripdistance,count(*)cnt from taxi_data group by tripdistance order by cnt desc limit 10")
    List<taxi> tripdistance();
    //7.最终费率代码统计
    @Select("select ratecodeid,count(*)cnt  from taxi_data group by ratecodeid order by cnt desc limit 10")
    List<taxi> ratecodeid();
    //8.提供商的代码统计
    @Select("select vendorid,count(*)cnt  from taxi_data group by vendorid order by cnt desc limit 10")
    List<taxi> vendorid();

    //9.上车时间统计
    @Select("select lpeppickupdatetime,count(*)cnt from taxi_data group by lpeppickupdatetime " +
            "order by cnt desc limit 10")
    List<taxi> lpeppickupdatetime();
    //10.下车时间统计
    @Select("select lpepdropoffdatetime,count(*)cnt from taxi_data group by lpepdropoffdatetime " +
            "order by cnt desc limit 10")
    List<taxi> lpepdropoffdatetime();
    //11.出租车区域统计-上车
    @Select("select pulocationid,count(*)cnt from taxi_data group by pulocationid order by cnt desc limit 20")
    List<taxi> pulocationid();
    //12.出租车区域统计-下车
    @Select("select dolocationid,count(*)cnt from taxi_data group by dolocationid order by cnt desc limit 20")
    List<taxi> dolocationid();
    //13.改进附加费统计
    @Select("select improvementSurcharge,count(*)cnt from taxi_data group by improvementSurcharge")
    List<taxi> improvementSurcharge();
    //14.标志指示统计
    @Select("select storeandfwdflag,count(*)cnt from taxi_data group by storeandfwdflag")
    List<taxi> storeandfwdflag();
    //15.税统计
    @Select("select mtatax,count(*)cnt from taxi_data group by mtatax")
    List<taxi> mtatax();
    //16.计价器计算的时间和距离费用统计
    @Select("select fareamount,count(*)cnt from taxi_data group by fareamount")
    List<taxi> fareamount();

    //17.付现金的乘客数量统计
    @Select("select passengercount,count(*)cnt  from taxi_data where paymenttype ='2'  group by passengercount")
    List<taxi> passengercount2();
    //18.免费的乘客数量统计
    @Select("select passengercount,count(*)cnt  from taxi_data where paymenttype ='3'  group by passengercount")
    List<taxi> passengercount3();
    //19.争议的乘客数量统计
    @Select("select passengercount,count(*)cnt  from taxi_data where paymenttype ='4'  group by passengercount")
    List<taxi> passengercount4();
    //20.失效行程的乘客数量统计
    @Select("select passengercount,count(*)cnt  from taxi_data where paymenttype ='6'  group by passengercount")
    List<taxi> passengercount6();
    //21.信用卡的乘客数量统计
    @Select("select passengercount,count(*)cnt  from taxi_data where paymenttype ='1'  group by passengercount")
    List<taxi> passengercount1();
    //22.付款未知的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where paymenttype ='5'  group by passengercount")
    List<taxi> passengercount5();

    //23.接头招呼乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where triptype ='1'  group by passengercount")
    List<taxi> passengercounttriptype1();
    //24.派单的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where triptype ='2'  group by passengercount")
    List<taxi> passengercounttriptype2();

    //25.拼车的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where ratecodeid ='6'  group by passengercount")
    List<taxi> passengercountratecodeid6();
    //26.标准费率的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where ratecodeid ='1'  group by passengercount")
    List<taxi> passengercountratecodeid1();
    //27.JFK的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where ratecodeid ='2'  group by passengercount")
    List<taxi> passengercountratecodeid2();
    //28.Newark的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where ratecodeid ='3'  group by passengercount")
    List<taxi> passengercountratecodeid3();
    //29.Nassau的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where ratecodeid ='4'  group by passengercount")
    List<taxi> passengercountratecodeid4();
    //30.议价票价的乘客数量
    @Select("select passengercount,count(*)cnt  from taxi_data where ratecodeid ='5'  group by passengercount")
    List<taxi> passengercountratecodeid5();

    //31.上车区域乘客数据总计
    @Select("select pulocationid,sum(passengercount)cnt  from taxi_data  group by pulocationid")
    List<taxi> puLocationidsum();
    //32.下车区域乘客数据总计
    @Select("select doLocationid,sum(passengercount)cnt  from taxi_data  group by doLocationid")
    List<taxi> doLocationidsum();

}
